package cn.hxzy.dao;

import cn.hxzy.entity.Work;
import cn.hxzy.util.DataSource;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class WorkDao extends BaseDao<Work> {

    public Work findByIdWithUser(int id) throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT w.*,u.`name` user_name FROM`work` w LEFT JOIN `user` u ON w.`user_id`=u.`id` WHERE w.`id`=?");
        statement.setObject(1, id);
        ResultSet resultSet = statement.executeQuery();
        Work work = null;
        if (resultSet.next()) {
            work = new Work(resultSet.getString("name"), resultSet.getString("work_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            work.setId(resultSet.getInt("id"));
//            work.setCreatTime(resultSet.getTimestamp("creat_time"));
            work.setCreatTime(resultSet.getTimestamp("creat_time"));
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        System.out.println(work);
        return work;
    }

    public List<Work> findAllWithUser() throws SQLException {
        Connection connection = DataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT w.*, u.`name` user_name FROM `work` w LEFT JOIN USER u ON w.`user_id`=u.`id`");
        ResultSet resultSet = statement.executeQuery();
        List<Work> list = new ArrayList<>();
        while (resultSet.next()) {
//            String name,  String workValue, Integer userId,String userName
            Work work = new Work(resultSet.getString("name"), resultSet.getString("work_value"), resultSet.getInt("user_id"), resultSet.getString("user_name"));
            work.setId(resultSet.getInt("id"));
            work.setCreatTime(resultSet.getDate("creat_time"));
            list.add(work);
        }
        resultSet.close();
        statement.close();
        DataSource.closeConnection(connection);
        return list;

    }

}
